from flask import Blueprint, render_template, request, jsonify, redirect, url_for
from datetime import datetime, timedelta
import sqlite3


# 创建蓝图
dashboard_blueprint = Blueprint('dashboard', __name__)
# 配置数据库连接
db_path = 'db/shop.db'

def get_db_connection():
    # 连接数据库并查询商品信息
    conn = sqlite3.connect(db_path, timeout=10)
     # 设置row_factory，使查询结果返回字典而不是元组
    conn.row_factory = sqlite3.Row
    return conn

@dashboard_blueprint.route('/dashboard')
def dashboard():
    return render_template('/dashboard.html')

@dashboard_blueprint.route('/dashboard/chart1')
def chart1():
    conn = get_db_connection()
    c = conn.cursor()
    
    # 查询每种类型保单的数量
    car_count = c.execute('SELECT COUNT(*) FROM car_insurance').fetchone()[0]
    home_count = c.execute('SELECT COUNT(*) FROM home_insurance').fetchone()[0]
    life_count = c.execute('SELECT COUNT(*) FROM life_insurance').fetchone()[0]
    
    conn.close()

    # 将数据返回给前端
    data = []
    data.append({"types":"车辆保险", "counts":car_count})
    data.append({"types":"房屋保险", "counts":home_count})
    data.append({"types":"人寿保险", "counts":life_count})
    return jsonify(data)

@dashboard_blueprint.route('/dashboard/chart3')
def chart3():
    conn = get_db_connection()
    c = conn.cursor()
    # 获取车险的总保险金额
    c.execute("SELECT SUM(CAST(quote AS REAL)) FROM car_insurance")
    car_total = c.fetchone()[0] or 0

    # 获取房屋险的总保险金额
    c.execute("SELECT SUM(CAST(quote AS REAL)) FROM home_insurance")
    home_total = c.fetchone()[0] or 0

    # 获取人寿险的总保险金额
    c.execute("SELECT SUM(CAST(quote AS REAL)) FROM life_insurance")
    life_total = c.fetchone()[0] or 0

    conn.close()

    data = []
    data.append({"types":"车辆保险", "total":car_total})
    data.append({"types":"房屋保险", "total":home_total})
    data.append({"types":"人寿保险", "total":life_total})
    return jsonify(data)